Navigating the PostgreSQL Seas: A Quest To Hidden Treasures

logo EDB

Who am I

Image by Anemone123 from Pixabay

The treasure map

  • Small gems
  • Text Quoting
  • Pattern matching
  • Complex datatypes handling
  • Upsert/MERGE

Small Gems

Image by Peter Lomas from Pixabay
logo EDB

DDL is transactionnal

laetitia=# create table test(id integer generated always as identity);
laetitia=# table test;
(0 rows) 
Image by Dieter Staab from Pixabay
logo EDB

DDL is transactionnal

laetitia=# begin;

laetitia=*# drop table test;

laetitia=*# table test;
2024-04-11 13:05:20.589 CEST [18422] ERROR:  relation "test" does not exist at character 7
2024-04-11 13:05:20.589 CEST [18422] STATEMENT:  table test;
ERROR:  relation "test" does not exist
LINE 1: table test;

laetitia=!# rollback;
laetitia=# table test;
(0 rows)
Image by Dieter Staab from Pixabay
logo EDB


COMMENT ON LANGUAGE plpython IS 'Python support for stored procedures';

COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus';

COMMENT ON DATABASE my_database IS 'Development Database';

COMMENT ON ROLE my_role IS 'Administration group for finance tables';
Image by ha11ok from Pixabay
logo EDB

Specialized indexes

  • multicolumns
  • partial
  • covering
  • expression
  • special ops
  • b-tree, BRIN, hash, GiST, GIN, SP-GiST
Image by NatureFriend from Pixabay
logo EDB

Foreign Data Wrappers

Access ALL YOUR DATA (really, all your data, we are not joking) from EVERYWHERE to Postgres!
Image by Robert Ramsay from Pixabay
logo EDB

Server-side programming

  • PL/Java
  • PL/Python
  • PL/R
  • PL/pgSQL (like PL/SQL)
  • PL/Ruby
  • PL/Scheme
  • PL/sh
  • PL/Tcl
  • PL/v8 (JavaScript)
  • SPI (C)
logo EDB

Your precious ship: text quoting

Image by Anja from Pixabay
logo EDB

Text quoting

  • " (double quote) for identifiers
  • ' (quote) for strings
  • escape character: \
  • escaping quotes: ''
logo EDB

Dollar-Quoted String

$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$
Image by Michio from Pixabay
logo EDB

Your straw hat: text searching

logo EDB

Like and ilike

  • like (with or without _ or %)
  • ilike
  • ~~ and ~~*
  • !~~ and !~~*
Image by Olle August from Pixabay
logo EDB

Similar to

string SIMILAR TO pattern [ESCAPE escape-character]

string NOT SIMILAR TO pattern [ESCAPE escape-character]
logo EDB

Similar to

  • | for alternation
  • *, +, ?, {m,n} for repetition
  • () for groups
  • [] for a character class
logo EDB

Proper regular expressions

  • ~ operator
    'thomas' ~ 't.*ma' → t
  • ~* operator
    'thomas' ~ 't.*ma' → t
logo EDB

Proper regular expressions

  • !~ operator
    'thomas' !~ 't.*max' → t
  • !~* operator
    'thomas' !~* 'T.*ma' → f
logo EDB

Regular expression functions

regexp_count regexp_replace
regexp_instr regexp_replace
regexp_like regexp_split_to_array
regexp_match regexp_split_to_table
Image by Olle August from Pixabay
logo EDB

Your crew: complex data types handling

Image by William Adams from Pixabay
logo EDB


SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';
logo EDB

JSON path

  • $.track.segments: retrieves the available track segments
  • $.track.segments[*].location: retrieves the contents of an array
  • $.track.segments[1].location: returns the coordinates of the first segment only
  • ? (condition): filters
logo EDB

JSON Tables

  • Query JSON data in SQL
  • Not in Postgres 15
  • Not in Postgres 16
  • Hopefully in postgres 17 🤞
logo EDB

Geographical data

  • PostGIS
  • Mix of spatial queries and traditional SQL
  • "What are the cities within 150 KM of Boston with the 10 lowest medium home prices?"
logo EDB

Geographical data

select name,
from interesting_cities
where (
  select ST_Distance (
      ST_Transform (location, 3587),
      ST_Transform( (
        select location
        from interesting_cities
        where name = 'Boston'), 3587)
  ) ) < 150000
order by medium_hval
limit 10;
logo EDB

Geographical data

logo EDB

The one piece: Upserts/MERGE

Image by Dean Moriarty from Pixabay
logo EDB

Inserts tricks

  • on conflict
  • returning
  • CTEs
logo EDB


WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
logo EDB


INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
logo EDB


MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
Image by Annette from Pixabay
logo EDB

Thank you!

logo EDB